package com.lavegue.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.lavegue.common.BaseHibernateDao;
import com.lavegue.common.Page;
import com.lavegue.dao.MsgDao;
import com.lavegue.model.BisType;
import com.lavegue.model.EveryDayConsumptionAggregate;
import com.lavegue.model.Msg;
import com.sun.org.apache.bcel.internal.generic.NEW;
import com.sun.org.apache.xml.internal.serialize.OutputFormat.DTD;

public class MsgDaoImpl extends BaseHibernateDao<Msg, Integer> implements MsgDao{

	public MsgDaoImpl() {
		super(Msg.class);
	}

	/**20111226
	 * 通过用户ID查询短信消息
	 * @return
	 */
	public List<Msg> findMsgByUid(Integer uid) {
		try {
			//对时间进行排序
			String queryString = "from Msg m where sender.id=? order by m.sendTime desc";
			List<Msg> list = this.getHibernateTemplate().find(queryString,uid);
			if(!list.isEmpty()){
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return null;
	}
	
	/**ljp 20111227
	 * 查询发送者用的所有彩信信息
	 * @param pageNo    当前页数
	 * @param pageSize  每页多少条
	 * @param titleName 彩信标题
	 * @param beginTime 选择开始时间
	 * @param endTime   选择结束时间
	 * @param senderUid  发送者的用户ID
	 * @return
	 */
	public Page<Msg> findMsgList(int pageNo, int pageSize, String titleName,
			Date beginTime, Date endTime, int senderUid) throws Exception{
		List<Criterion> condition = new ArrayList<Criterion>();
		//条件查询
		if(null != titleName && !titleName.trim().equals("")){  //彩信标题
			  condition.add(Restrictions.like("title", titleName, MatchMode.ANYWHERE));
		}
		
		// beginTime< sendTime <endTime		
		if(null != beginTime && null != endTime){
			condition.add(Expression.between("sendTime", beginTime, endTime));
		}else{
			if(null != beginTime){  //选择开始时间
				//查询某一天的记录[sendTime >= beginTime and sendTime < beginTime+一天的时间]
				condition.add(Expression.and(Expression.ge("sendTime", new java.sql.Date(beginTime.getTime())),Expression.lt("sendTime", new java.sql.Date(beginTime.getTime()+1000*60*60*24))));
			}
			if(null != endTime){   //选择结束时间
				  condition.add(Restrictions.and(Expression.ge("sendTime", new java.sql.Date(endTime.getTime())),Expression.lt("sendTime", new java.sql.Date(endTime.getTime()+1000*60*60*24))));
			}
		}
		
		if(senderUid != 0){  //发送者的用户ID
			condition.add(Restrictions.eq("sender.id", Integer.valueOf(senderUid)));
		}
		//查询列表,通过时间排序
		Page<Msg> page = this.findPage(pageNo, pageSize, condition, Order.desc("sendTime"));
		return page;
	}

	/**ljp 20111228
	 * 查询发送者用户的 每日消费汇总 信息
	 * @param pageNo    当前页数
	 * @param pageSize  每页多少条
	 * @param dateTime  选择时间
	 * @param senderUid  发送者的用户ID
	 * @return
	 */
	public Page<EveryDayConsumptionAggregate> findEveryDayConsumptionList(int pageNo, int pageSize,
			Date dateTime, int senderUid) throws Exception {
		List<Criterion> condition = new ArrayList<Criterion>();
			//条件查询	
			if(null != dateTime){  //选择开始时间
				//查询某一天的记录[sendTime >= dateTime and sendTime < dateTime+一天的时间]
				condition.add(Expression.and(Expression.ge("sendTime", dateTime),Expression.lt("sendTime", new Date(dateTime.getTime()+1000*60*60*24))));
			}
			if(senderUid != 0){  //发送者的用户ID
				condition.add(Restrictions.eq("sender.id", Integer.valueOf(senderUid)));
			}
			
		//查询列表,通过时间排序
		Page<EveryDayConsumptionAggregate> page = findPage(pageNo, pageSize, condition, Order.desc("sendTime"),"sendTime","title");
		return page;
	}
	
	
	/**
	 *  分页 分组分页功能 
	 * @param pageNo当前页数
	 * @param pageSize 页面显示的条数
	 * @param conditions 查询条件
	 * @param order 排序
	 * @param groupFieldName 分组的字段名
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<EveryDayConsumptionAggregate> findPage(final int pageNo, final int pageSize, final List<Criterion> conditions, final Order order,final String groupFieldName1,final String groupFieldName2) {
		final int total = this.getTotal(conditions);
		final int totalPage = countPage(total, pageSize);
		List<Msg> list= (List<Msg>) this.getHibernateTemplate().execute(new HibernateCallback() {
			
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(entityClass);
				if (conditions != null) {
					for (Criterion c : conditions) {
						criteria.add(c);
					}
				}
				if (order != null) {
					criteria.addOrder(order);
				}
				//设置字段分组
				if(null != groupFieldName1 && !groupFieldName1.equals("")){
					ProjectionList projList = Projections.projectionList();  
					//字段分组 as是给字段取别名
					projList.add(Projections.groupProperty(groupFieldName1)).add(Projections.groupProperty(groupFieldName2));
				    projList.add(Projections.count(groupFieldName2).as("sendNumber"));   //统计一个属性在结果中出现的次数。
				    projList.add(Projections.property("price").as("price"));    //发送单价
				    projList.add(Projections.sum("price").as("groupConsumptionMoney"));   //统计每日发送彩信的每组消费金额。
				    criteria.setProjection(projList); 

				}
				
				//如果页数大于总页数，则取最后一页
				if (pageNo > totalPage) {
					criteria.setFirstResult(computeStart(totalPage, pageSize));
				} else {
					criteria.setFirstResult(computeStart(pageNo, pageSize));
				}
				criteria.setMaxResults(pageSize);
				return criteria.list();
			}
		});
		////如果页数大于总页数，则取最后一页
		int pageNoNew = (pageNo > totalPage)?  totalPage: pageNo;
		Page<EveryDayConsumptionAggregate> page = new Page(pageNoNew, total, list, pageSize);
		List dataMsg = page.getData();
		List ecalist =  new ArrayList<EveryDayConsumptionAggregate>();
		for (Object o : dataMsg) {
			EveryDayConsumptionAggregate eca = new EveryDayConsumptionAggregate();
			Object[] arr = (Object[]) o;
			Timestamp sendtime = (Timestamp)arr[0]; //发送时间
			String title = (String)arr[1];  //彩信标题
			Integer sendNum = (Integer)arr[2]; //发送条数
			BigDecimal price = (BigDecimal)arr[3]; //发送单价
			BigDecimal consumptionMoney = (BigDecimal)arr[4]; //消费金额
			eca.setSendTime(sendtime);
			eca.setTitle(title);
			eca.setPrice(price);
			eca.setSendCount(sendNum);
			eca.setConsumptionMoney(consumptionMoney);
					
			ecalist.add(eca);
		}
		page.setData(ecalist);
		return page;
	}
	
	/**20111230
	 * 统计当天消费汇总
	 */
	public BigDecimal getDtConsumptionMoney(int senderUid){
		BigDecimal	dangTianMoney =BigDecimal.valueOf(0.00);
		try {
			Date ddDate= new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
			String dtTime = sdf.format(ddDate);
			Date sysTime = sdf.parse(dtTime);
			
			String queryString = "select sum(price) as price from Msg m where sender="+senderUid
			+" and m.sendTime >= "+"'"+dtTime+"'";
			//+" and  m.sendTime < "+(ddDate.getTime()+1000*60*60*24) +")"
			List list = this.getHibernateTemplate().find(queryString);
			if(null != list && list.size()!=0){
				dangTianMoney = (BigDecimal)list.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return dangTianMoney;
	}
	
 	protected int computeStart(int pageNo, int pageSize) {
		return (pageNo - 1) * pageSize;
	}
	public int getTotal() {
		String sql = "select count(*) from " + this.entityClass.getName();
		List list = this.getHibernateTemplate().find(sql);
		return Integer.valueOf(list.get(0) + "");
	}
	//tzf 首页模糊查询根据标题
	public Page<Msg> findMsgByContent(int pageNo,int pageSize,int typeid,String likeName)
	{
		List<Criterion> condition = new ArrayList<Criterion>();
		if(typeid!=0){
			   condition.add(Restrictions.eq("type.id", typeid));
			}
		if(null != likeName && !likeName.trim().equals("")){
		   condition.add(Restrictions.like("title", likeName, MatchMode.ANYWHERE));
		}
		Page<Msg> page = this.findPage(pageNo, pageSize, condition, Order.desc("sendTime"));
		return page;
	}
	
	//tzf 首页模糊查询根据标题和内容
	public Page<Msg> findMsgByContentandTitle(final int pageNo,final int pageSize,int typeid,String likeName)
	{
       Page<Msg> ps=null;
       String query;
       if(typeid==0){
    	   query="FROM Msg m WHERE m.title LIKE '%"+likeName+"%' OR EXISTS (SELECT id FROM MsgDetail md WHERE m.id= md.msg.id AND (md.txt LIKE '%"+likeName+"%'))";
      }else
       {
   	   query="FROM Msg m WHERE m.type.id="+typeid+" and m.title LIKE '%"+likeName+"%' OR EXISTS (SELECT id FROM MsgDetail md WHERE m.id= md.msg.id AND (md.txt LIKE '%"+likeName+"%'))";
       }
       
       final String q1 = query;
       List<Msg> mlist =this.getHibernateTemplate().find(query);
       final int total =mlist.size();
       final int totalPage = countPage(total, pageSize);
       List<Msg> list= (List<Msg>) this.getHibernateTemplate().execute(new HibernateCallback() {
		public Object doInHibernate(Session s) throws HibernateException,
				SQLException {
			Query q2 = s.createQuery(q1);
			q2.setMaxResults(pageSize);
			q2.setFirstResult(computeStart(totalPage, pageSize));
			//如果页数大于总页数，则取最后一页
			if (pageNo > totalPage) {
				q2.setFirstResult(computeStart(totalPage, pageSize));
			} else {
				q2.setFirstResult(computeStart(pageNo, pageSize));
			}
			return q2.list();
		}
	});
    
   ////如果页数大于总页数，则取最后一页
		int pageNoNew = (pageNo > totalPage)?  totalPage: pageNo;
		ps = new Page<Msg>(pageNoNew, total, list, pageSize);
      return ps;
     }

	public Page<Msg> findMsgByTopUp(int pageNo, int pageSize, int topupid) {
		List<Criterion> condition = new ArrayList<Criterion>();
		condition.add(Restrictions.eq("topUp.id", topupid));
		Page<Msg> page = this.findPage(pageNo, pageSize, condition, Order.desc("sendTime"));
		return page;
	}
}
